var proxy = require('http-proxy-middleware');
var express = require('express');
var request = require('request');
var moment = require('moment');
var urlencode = require('urlencode');
var _ = require('underscore');
var fs = require('fs');
var redis = require('redis');
var http = require('http');
var url = require('url');
var querystring = require('querystring');
var redisclient = require('./redisclient.js');
var redisDB = require('./redisDB.js');
var SqliteDB = require('./sqlite.js').SqliteDB;
var cors = require('cors');

//获取express实例
var app = express();
//实现跨域访问
app.use(cors());

//设置路由
const router = express.Router();
//请求页面跑在3000端口
const requestPort = 8088;
//本机外网IP地址
const requestIPAddress = `120.24.90.29:${requestPort}`;
//常用修改路径
const cbasepath = '/GenerSoft/bscw_local/web/gspwf/en-US';
//获取DAO示例
const mssqldb = require('./mssql.js');

//数据库名称
const datbasefile = 'database.db';
//bizcode数据文件
const bizcodefile = 'bizcode.db';
//配置文件名称
const configfile = 'app.config';

//sqliteDB数据库实例对象
var sqliteDB = new SqliteDB(datbasefile);

//数据库账户密码
const username = 'LC00019999';
const password = 'Brcdata';

//已经推送的bizCode数组
var bizCodes = [];

//请求转发URL
var messageURL =
    'http://api.brc.com.cn:8088/open/gw/1/third/workflowAction!createTaskForThird.mbApp/?access_token={token}&platCode=third&appCode=com.brc.lanxin&timeStamp={timestamp}&body={"bizId":"{bizID}","executorLoginName":"{name}","redirect_uri":"{url}","needTiming":"{timetype}","description":"{message}","creatorUrl":"PC"}';

//转发URLs
var redirctURL = (redirctDFLTURL =
    '/cwbase/web/session/gsidp/gsys/web/gsycoldefshow.aspx?funcid=b310f658-5abe-40a7-bd03-fea3e5aec125add1552628448879%26bizObjectId=%26bizOPId=%26modid=7a0ebe55ee3345008e891f4eb3b0983b%26styleid=de184e4e7dec4a2eb5bc2339acb03ead%26id={itemID}%26opertype=edit%26BIZOBJ=%26iswf=');

//质安巡视转发URL Quality Visits
var redirctQLVSURL =
    '/cwbase/web/session/gsidp/gsys/web/gsycoldefshow.aspx?funcid=b310f658-5abe-40a7-bd03-fea3e5aec125add1552628448879%26bizObjectId=%26bizOPId=%26modid=7a0ebe55ee3345008e891f4eb3b0983b%26styleid=de184e4e7dec4a2eb5bc2339acb03ead%26id={itemID}%26opertype=edit%26BIZOBJ=%26iswf=';

//预警督办单转发URL Supervising
var redirctSPVSURL =
    '/cwbase/web/session/gsidp/gsys/web/gsycoldefshow.aspx?funcid=30a9034e-b785-4724-8876-98ea3b459a54edit1555310778725%26bizObjectId=%26bizOPId=%26modid=3a2a8319194841e7ae109079c47626ca%26styleid=3f9f4ee35f74005dedfc344a0d63ad43%26id={itemID}%26opertype=edit%26BIZOBJ=%26iswf=%26reject=true';

//示范专检转发URL  Demonstration Inspection
var redirctDSISURL =
    '/cwbase/web/session/gsidp/gsys/web/gsycoldefshow.aspx?funcid=9d44a2ce-0ac6-4348-aff6-30579d417d16add1553585000998%26bizObjectId=%26bizOPId=%26modid=95b6b825e89549b097847fdc1c20ee86%26styleid=9802d8ce7753479fae9c7f71faa1a912%26id={itemID}%26opertype=edit%26BIZOBJ=%26iswf=';

//工程整改转发URL Engineering Rectification
var redirctEGRCURL =
    '/cwbase/web/session/gsidp/gsys/web/gsycoldefshow.aspx?funcid=5a2a1ad6-3788-4cc6-a12a-4fe84a18f1c5view1553584270538%26bizObjectId=%26bizOPId=%26modid=498bf53e63a8485f80983df2a2298404%26styleid=ebaa792f81504d458ca15c67b3a4518d%26id={itemID}%26opertype=edit%26BIZOBJ=%26iswf=';

//品质回访转发URL Quality Access
var redirctQLACURL =
    '/cwbase/web/session/GSIDP/GSYS/WEB/GSYCOLDEFShow.aspx?funcid=785e4a61-4f2e-4793-b4a4-3e0844912fb8view1563448031416%26bizObjectId=%26bizOPId=%26modid=9ded0169025f445181aab145f5a801d2%26styleid=1226d625bb8e45b08f9956ed89f30d99%26id={itemID}%26opertype=edit%26BIZOBJ=%26iswf=';

//交付评估转发URL Delivery Evaluation
var redirctDLELURL =
    '/cwbase/web/session/gsidp/gsys/web/gsycoldefshow.aspx?funcid=8468b88b-77c2-4814-a738-fd8c1ef057a6add1553581337843%26bizObjectId=%26bizOPId=%26modid=4d35c2f014694983968ae0df4f04511b%26styleid=0239750a2837e3d01d258f4817327c8e%26id={itemID}%26opertype=edit%26BIZOBJ=%26iswf=';

//生产督察转发URL Production Inspector
var redirctPDIPURL =
    '/cwbase/web/session/gsidp/gsys/web/gsycoldefshow.aspx?funcid=93df7b0c-295b-4f09-ac6f-5b35a90cb232add1553581739479%26bizObjectId=%26bizOPId=%26modid=70054e946d5f41ad82f8cc7d7adfddad%26styleid=22658e07153146a9960624097bc6e109%26id={itemID}%26opertype=edit%26BIZOBJ=%26iswf=';

//项目维保URL Project Maintenance PJMN
var redirctPJMNURL =
    '/cwbase/web/session/GSIDP/GSYS/WEB/GSYCOLDEFShow.aspx?funcid=666ee358-0671-4322-9c6c-29c580c7b2e9edit1573437840531%26bizObjectId=%26bizOPId=%26modid=76a0354712bc411d8922912b096b9945%26styleid=117651b0703f4f958be4a0ba226feb4c%26id={itemID}%26opertype=edit%26BIZOBJ=%26iswf=';

//撤销推送消息URL（非计时待办）
var removeURL =
    'http://api.brc.com.cn:8088/open/workflow/changestatus_task?access_token={token}&id={bizID}&status=0';

//撤销推送消息URL（计时待办）
var cancelURL =
    'http://api.brc.com.cn:8088/open/gw/1/third/workflowAction!completeTaskForThird.mbApp/?access_token={token}&platCode=third&appCode=com.brc.lanxin&timeStamp={timestamp}&body={"bizId":"{bizID}","operateKind":"complete"}';

//单点登录地址URL
var loginURL =
    'http://erp.brc-beei.com:8082/cwbase/web/singlelogin.aspx?AuthType=UserMap&UserCode={usercode}&AppCode=0001&callType=1&callPara={realurl}';

//域名地址URL
var domainURL = 'http://erp.brc-beei.com:8082';

//统一RedirctURL
var singleURL =
    'http://{IPAddress}%3Ftoken%3D{token}%26type%3D{type}%26urltype%3D{urltype}%26timestamp%3D{timestamp}%26bizID%3D{bizID}%26itemID%3D{itemID}%26name%3D{name}%26message%3D{message}';

//记录推送消息表名
var createTableName = 'message_log';

//客户信息记录信息
var clientInfo = [];

//用户记录CRM_Plus_Contact
var contactInfo = [];

//设置跨域访问
router.all('*', function(req, res, next) {
    try {
        res.header('Access-Control-Allow-Origin', '*');
        res.header('Access-Control-Allow-Headers', 'X-Requested-With');
        res.header('Access-Control-Allow-Methods', '*');
        res.header('X-Powered-By', 'express');
        res.header('Content-Type', 'application/json;charset=utf-8');
        next();
    } catch (e) {
        console.log(e);
    }
});

//使用Express框架
app.use(express.static(__dirname));

//监听端口
app.listen(requestPort, function() {
    try {
        //读取文件中的bizcode数据
        var data = fs.readFileSync(bizcodefile, 'utf8');
        //解析原bizCode内容，并放入内存中
        bizCodes = JSON.parse(data);
        //打印启动信息
        console.log(
            'Requester is listening on port ' + requestPort + ' Init data : ' + data
        );
    } catch (e) {
        console.log(e);
    }
});

//监听'/'路径下的所有请求，并转发请求到http://api.brc.com.cn:8088服务器上
app.get('/', (req, res) => {
    try {
        //打印请求URL
        console.log('请求URL: ' + req.protocol + '://' + req.headers.host + req.url);

        //console.log('cache : ' + req.query.cache);

        //如果传入参数不为空，则执行函数
        if (typeof req.query.type != 'undefined') {
            //执行操作
            eval(req.query.type + '(req , res)');
        } else if (typeof req.query.cache != 'undefined') {
            //查询缓存
            var cache = queryCache(req, res);
            //返回请求参数信息
            return res.send(cache);
        } else if (typeof req.query.name != 'undefined') {
            //处理楼盘相关业务
            return housesBackend(req, res);
        } else {
            //没有获取到执行操作的请求参数，返回信息
            return res.send('welcome express api');
        }
    } catch (e) {
        console.log(e);
    }
});

/**
 * @function 处理楼盘相关业务函数
 */
function housesBackend(req, res) {
    //获取用户ID
    var userID = req.query.userID;
    //获取查询的楼盘ID
    var housesID = req.query.housesID;
    //获取查询开始时间
    var startTime = req.query.startTime;
    //获取查询的结束时间
    var endTime = req.query.endTime;

    //查询楼盘信息
    if (req.query.name == 'queryHousesInfo') {
        //查询楼盘信息
        var info = queryHousesInfo();
        //返回查询的楼盘信息
        return res.send(info);
    } else if (req.query.name == 'queryHousesLoginInfo') {
        //查询楼盘的用户登录情况
        var info = queryHousesLoginInfo(housesID, startTime, endTime);
        //返回查询的用户登录信息
        return res.send(info);
    } else if (req.query.name == 'queryHousesLoginIndex') {
        //查询楼盘的用户登录指标情况
        var info = queryHousesLoginIndex(housesID, startTime, endTime);
        //返回查询的用户登录指标信息
        return res.send(info);
    } else if (req.query.name == 'queryHousesTopicIndex') {
        //获取话题指标信息
        var info = queryHousesTopicIndex(housesID, startTime, endTime);
        //返回查询的话题信息
        return res.send(info);
    } else if (req.query.name == 'queryHousesTopicTypeIndex') {
        //获取话题类型指标
        var info = queryHousesTopicTypeIndex(userID, startTime, endTime);
        //返回指标信息
        return res.send(info);
    }
}

/**
 * @function 查询缓存信息
 */
function queryCache(req, res) {
    var tableName = '';

    //缓存参数为clientinfo
    if (req.query.cache == 'clientinfo') {
        //客户表(基础表) //select top 10 * from CRM_ClientInfo;
        tableName = 'CRM_ClientInfo';
    } else if (req.query.cache == 'contactinfo') {
        //用户信息 //select top 10 * from CRM_Plus_Contact; --Vest true 马甲  false 真实用户  --FistTime 激活
        tableName = 'CRM_Plus_Contact';
    } else if (req.query.cache == 'topicinfo') {
        //话题表 //select top 10 * from SNS_Plus_Topic; --HOUSELPGID  --CREATORTYPE 0 用户发表 1 后台发表 --TopicType 分析维度 话题分类 --audit 审核 --state 审核
        tableName = 'SNS_Plus_Topic';
    } else if (req.query.cache == 'discussinfo') {
        //评论表 //select top 10 * from SNS_Plus_Discuss; --CreatorType 0 用户  1 后台  --TargetID
        tableName = 'SNS_Plus_Discuss';
    } else if (req.query.cache == 'praiseinfo') {
        //点赞记录表 //select top 10 * from SNS_Plus_TopicPraise;
        tableName = 'SNS_Plus_TopicPraise';
    } else if (req.query.cache == 'enshrineinfo') {
        //收藏话题表 //select top 10 * from SNS_Plus_Enshrine; --OwnerID means ContactID --TargetID --type 1 话题
        tableName = 'SNS_Plus_Enshrine';
    } else if (req.query.cache == 'readloginfo') {
        //话题浏览数 //select top 10 * from SNS_Plus_ReadLog;
        tableName = 'SNS_Plus_ReadLog';
    } else if (req.query.cache == 'loginloginfo') {
        //登录日志表 //select top 10 * from SYS_Plus_LoginLog;
        tableName = 'SYS_Plus_LoginLog';
    } else if (req.query.cache == 'housesinfo') {
        //楼盘表 //select top 10 * from Base_HouseLP;
        tableName = 'Base_HouseLP';
    } else if (req.query.cache == 'projectinfo') {
        //工程表 //select top 10 * from PRJ_ProjectInfo where fstate = 1; --fstate 1 正常 ； 其他 异常  --fclientgid 客户ID  --flpgid 楼盘ID
        tableName = 'PRJ_ProjectInfo';
    }

    //获取客户端信息
    return queryDatabase(tableName);
}

/**
 * @function 查询数据库操作
 */
function queryDatabase(tableName, selectSQL) {
    //获取客户端信息
    var clientinfo = redisclient.get(tableName);
    //获取查询SQL
    selectSQL = selectSQL || 'select top 1000 * from ';

    //console.log(' key lenght :' + cinfolength);
    if (
        typeof clientinfo == 'undefined' ||
        clientinfo.length == 0 ||
        Object.keys(clientinfo).length == 0 ||
        clientinfo == null
    ) {
        //查询客户信息
        mssqldb.sql(selectSQL + tableName, function(err, result) {
            if (err) {
                //打印错误日志
                console.log('error : ' + err);
            } else {
                //设置客户信息缓存
                redisclient.set(tableName, result);
            }
        });
    } else {
        //缓存中获取到客户信息
    }

    return clientinfo;
}

/**
 * @function 查询数据库表信息操作
 */
function queryDBInfo(querySQL) {
    //获取客户端信息
    var clientinfo = redisclient.get(tableName);

    //console.log(' key lenght :' + cinfolength);
    if (
        typeof clientinfo == 'undefined' ||
        clientinfo.length == 0 ||
        Object.keys(clientinfo).length == 0 ||
        clientinfo == null
    ) {
        //查询客户信息
        mssqldb.sql(querySQL, function(err, result) {
            if (err) {
                //打印错误日志
                console.log('error : ' + err);
            } else {
                //设置客户信息缓存
                redisclient.set(tableName, result);
            }
        });
    } else {
        //缓存中获取到客户信息
    }

    return clientinfo;
}

/**
 * @function 获取楼盘列表信息
 */
function queryHousesInfo() {
    //查询楼盘列表信息
    var houses = queryDatabase('Base_HouseLP', 'select fname , fgid from ');

    console.log('houses info :' + houses);

    return houses;
}

/**
 * @function 获取某楼盘的用户登录信息
 */
function queryHousesLoginInfo(housesID, startTime, endTime) {
    //检查housesID , startTime , endTime是否符合规范

    //查询SQL语句
    var sql = `select ContactID , count(ContactID) total , max(log.LoginTime) time from SYS_Plus_LoginLog log
                    where
                        ContactID in (
                            select ContactID from CRM_Plus_ClientContactRelation 
                                where 
                            ClientInfoFGID in (select FClientGID from PRJ_ProjectInfo where fstate = 1 and flpgid = '${housesID}')
                        )
                    and LoginTime >= ${startTime} and LoginTime <= ${endTime}        
                    group by ContactID `;

    //获取数据库的查询结果
    var info = queryDBInfo(sql);

    //返回结果
    return info;
}

/**
 * @function 获取某楼盘的登录统计分析信息
 * @param {*} req
 * @param {*} res
 */
function queryHousesLoginIndex(housesID, startTime, endTime) {
    //检查housesID , startTime , endTime是否符合规范

    //获取登录信息指标
    var sql = ` select * from SYS_Plus_LoginLog 
                    where 
                        ContactID in (
                            select ContactID from CRM_Plus_ClientContactRelation 
                                where 
                            ClientInfoFGID in (select FClientGID from PRJ_ProjectInfo where fstate = 1 and flpgid = '${housesID}')
                        )
                    and LoginTime >= '${startTime}' and LoginTime <= '${endTime}' `;

    //获取数据库的查询结果
    var info = queryDBInfo(sql);

    //返回统计的分析指标
    return info;
}

/**
 * @function 获取话题各个类型的统计指标
 */
function queryHousesTopicTypeIndex(userID, startTime, endTime) {
    //检查userID , startTime , endTime是否符合规范

    //查询用户话题统计指标
    var sql = `select distinct t.TopicType , isnull(t.total,0) total , isnull(d.total,0) discuss ,  isnull(p.total,0) as Praise  , isnull(e.total,0) Enshrine , isnull(r.total,0) readlog , isnull(kp.total,0) xPraise , isnull(ep.total , 0) xEnshrine from
                (select TopicType , count(id) total from 
                                SNS_Plus_Topic where createTime >= ${startTime} and createTime <= ${endTime} and CreatorID = ${userID} 
                            group by TopicType) t
                full join
                (select v.TopicType , count(TopicID) total from
                                (select d.topicid , d.CreatorID , t.TopicType from SNS_Plus_Discuss d  where createTime >= ${startTime} and createTime <= ${endTime} and CreatorID = ${userID} left join SNS_Plus_Topic t on t.ID = d.TopicID) v
                            group by v.TopicType ) d on t.TopicType = d.TopicType
                full join
                (select v.TopicType , count(TopicID) total from
                                (select d.topicid , d.ContactID CreatorID , t.TopicType from SNS_Plus_ReadLog d  where createTime >= ${startTime} and createTime <= ${endTime} and ContactID = ${userID} left join SNS_Plus_Topic t on t.ID = d.TopicID) v
                            group by v.TopicType) p on t.TopicType = p.TopicType
                full join
                (select v.TopicType , count(TopicID) total from
                                (select d.TargetID topicid , d.OwnerID CreatorID , t.TopicType from SNS_Plus_Enshrine d where createTime >= ${startTime} and createTime <= ${endTime} and OwnerID = ${userID} left join SNS_Plus_Topic t on t.ID = d.TargetID) v
                            group by v.TopicType) e on t.TopicType = e.TopicType
                full join
                (select v.TopicType , count(TopicID) total from
                                (select d.topicid , d.ContactID CreatorID , t.TopicType from SNS_Plus_TopicPraise d where createTime >= ${startTime} and createTime <= ${endTime} and ContactID = ${userID} left join SNS_Plus_Topic t on t.ID = d.TopicID) v
                            group by v.TopicType) r on t.TopicType = r.TopicType
                full join 
                (select v.TopicType , count(TopicID) total from
                                (select p.TopicID , t.CreatorID , t.TopicType from SNS_Plus_TopicPraise p where createTime >= ${startTime} and createTime <= ${endTime} and CreatorID = ${userID} left join SNS_Plus_Topic t on t.id = p.TopicID) v
                            group by v.TopicType) kp on t.TopicType = kp.TopicType
                full join 
                (select v.TopicType , count(TopicID) total from
                                (select d.TargetID topicid , t.CreatorID , t.TopicType from SNS_Plus_Enshrine d where createTime >= ${startTime} and createTime <= ${endTime} and CreatorID = ${userID} left join SNS_Plus_Topic t on t.ID = d.TargetID) v
                            group by v.TopicType) ep on t.TopicType = ep.TopicType    
                            `;

    //获取数据库的查询结果
    var info = queryDBInfo(sql);

    //返回统计的分析指标
    return info;
}

/**
 * @function 获取某楼盘的话题统计分析信息
 */
function queryHousesTopicIndex(housesID, startTime, endTime) {
    //检查housesID , startTime , endTime是否符合规范

    //获取楼盘话题统计指标
    var sql = `select t.TopicID , t.Content , t.housesID , isnull(d.total,0) discuss ,  isnull(p.total,0) as Praise  , isnull(e.total,0) Enshrine , isnull(r.total,0) readlog from
                (select id TopicID , Content , Base_HouseLPFGID housesID from SNS_Plus_Topic) t
                full join
                (select TopicID , count(id) total from SNS_Plus_Discuss where createTime >= ${startTime} and createTime <= ${endTime} group by TopicID) d on t.TopicID = d.TopicID
                full join
                (select TopicID , count(id) total from SNS_Plus_ReadLog where createTime >= ${startTime} and createTime <= ${endTime} group by TopicID) p on t.TopicID = p.TopicID
                full join
                (select TargetID as TopicID, count(id) total from SNS_Plus_Enshrine where createTime >= ${startTime} and createTime <= ${endTime} group by TargetID) e on t.TopicID = e.TopicID
                full join
                (select TopicID  , count(id) total from SNS_Plus_TopicPraise where createTime >= ${startTime} and createTime <= ${endTime} group by TopicID) r on t.TopicID = r.TopicID`;

    //获取楼盘指标的查询结果
    var info = queryDBInfo(sql);

    //返回统计结果
    return info;
}

/**
 * @function 质安巡视推送消息函数
 */
function securityCheckMessage(req, res) {
    try {
        //需要记录下来的bizID，当需要取消推送消息时，需要取消
        const bizID = req.query.bizID;

        //获取itemID信息
        const itemID = req.query.itemID;

        //名字标识
        const nameFlag =
            typeof req.query.name == 'undefined' || req.query.name == 'undefined';

        //设置回调URL
        const rurl = redirctURL
            .replace('{itemID}', itemID)
            .replace('%7bitemID%7d', itemID)
            .replace('%7BitemID%7D', itemID);

        //SinginLogonURL
        const realURL = loginURL
            .replace('{usercode}', req.query.name)
            .replace('%7Busercode%7D', req.query.name)
            .replace('%7busercode%7d', req.query.name)
            .replace('{realurl}', rurl)
            .replace('%7Brealurl%7D', rurl)
            .replace('%7brealurl%7d', rurl);

        //检查bizCodes是否存在于内存中
        if (_.contains(bizCodes, bizID)) {
            //打印日志
            console.log(
                ' this bizcode is already exist in memory , bizcode : ' + bizID
            );
            //返回消息
            return res.send(
                ' this bizcode is already exist in memory , send message fail . '
            );
        } else if (nameFlag) {
            //打印日志
            console.log(' this username is undefined , bizcode : ' + bizID);
            //返回消息
            return res.send(' this username is undefined , send message fail . ');
        } else if (!_.contains(bizCodes, bizID)) {
            //将bizID放入bizCodes数组中
            bizCodes.push(bizID);

            //向数据库中查询，bizID是否已经存在，如果存在，则不在推送消息
            sqliteDB.selectNewTable(
                createTableName,
                ' bizCode = ? ', [bizID],
                function(flag, data) {
                    //没有获取到数据，表示此消息没有被推送过，则发送消息
                    if (flag == false) {
                        //推送消息
                        sendMessage(flag, data, req, res);
                    } else {
                        console.log(
                            ' find data from database , server will not send message to oa system . '
                        );
                    }
                }
            );

            //设置返回数据
            return res.send(' send message success , over . ');
        } else {
            //打印日志
            console.log(
                ' this bizcode maybe already exist in memory , there maybe exist concurrent access , bizcode : ' +
                bizID
            );
            //返回消息
            return res.send(
                ' this bizcode maybe already exist in memory , there maybe exist concurrent access , send message fail . '
            );
        }
    } catch (error) {
        console.log(error);
    }
}

/**
 * @function 推送非计时待办消息
 */
function sendMessage(flag, data, req, res) {
    try {
        //转发URL
        let redirctURL = queryRedirctURL(req, res);

        //需要记录下来的bizID，当需要取消推送消息时，需要取消
        const bizID = req.query.bizID;

        //获取itemID信息
        const itemID = req.query.itemID;

        //设置回调URL
        const rurl = redirctURL
            .replace('{itemID}', itemID)
            .replace('%7bitemID%7d', itemID)
            .replace('%7BitemID%7D', itemID);

        //SinginLogonURL
        const realURL = loginURL
            .replace('{usercode}', req.query.name)
            .replace('%7Busercode%7D', req.query.name)
            .replace('%7busercode%7d', req.query.name)
            .replace('{realurl}', rurl)
            .replace('%7Brealurl%7D', rurl)
            .replace('%7brealurl%7d', rurl);

        //OA SYSTEM CALLBACK URL
        const showURL = singleURL
            .replace('{IPAddress}', requestIPAddress)
            .replace('{token}', req.query.token)
            .replace('{type}', 'redirctOperation')
            .replace('{urltype}', req.query.urltype)
            .replace('{timestamp}', 0)
            .replace('{bizID}', req.query.bizID)
            .replace('{itemID}', req.query.itemID)
            .replace('{name}', req.query.name)
            .replace('{message}', urlencode(req.query.message));

        //设置请求地址
        const msgURL = messageURL
            .replace('{token}', req.query.token)
            .replace('{timestamp}', req.query.timestamp)
            .replace('{bizID}', bizID)
            .replace('{timetype}', '0')
            .replace('{name}', req.query.name)
            .replace('{url}', showURL)
            .replace('{itemID}', itemID)
            .replace('{message}', urlencode(req.query.message));

        //数据库需要记录数据
        const valuearg = [
            bizID,
            req.query.name,
            bizID,
            itemID,
            req.query.timestamp,
            req.query.message,
            'non-time',
            showURL,
            moment().format('YYYYMMDDHHmmssSSS'),
            req.query.urltype,
        ];

        //发送Message请求
        request(msgURL, function(error, response, body) {
            //如果请求没有报错，则返回相应结果
            if (!error && response.statusCode == 200) {
                //将bizCodes写入文件中
                fs.writeFileSync(bizcodefile, JSON.stringify(bizCodes));
                //推送消息成功，向数据库中记录推送的bizID
                sqliteDB.insertNewTable(
                    createTableName,
                    ' ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , 0 ',
                    valuearg,
                    ' bizCode = ? ', [bizID]
                );
                //Show the HTML for the baidu homepage.
                console.log('result info：' + body);
                //响应:无需html响应头，默认设置text/html
                //return res.send(body);
            } else {
                //return res.send(" error ");
            }
        });

        //打印相关参数
        console.log(
            'redirect URL: ' +
            rurl +
            '\n' +
            'bizID:' +
            req.query.bizID +
            '\n itemID:' +
            req.query.itemID +
            '\n request URL: ' +
            msgURL
        );
    } catch (error) {
        console.log(error);
    }
}

/**
 * @function 质安巡视取消推送消息
 */
function securityRemoveMessage(req, res) {
    try {
        //需要记录下来的bizID，当需要取消推送消息时，需要取消
        const bizID = req.query.bizID;

        //获取itemID信息
        const itemID = req.query.itemID;

        //设置回调URL
        const rurl = redirctURL.replace('{itemID}', itemID);

        //SinginLogonURL
        const realURL = loginURL
            .replace('{usercode}', req.query.name)
            .replace('%7Busercode%7D', req.query.name)
            .replace('%7busercode%7d', req.query.name)
            .replace('{realurl}', rurl)
            .replace('%7Brealurl%7D', rurl)
            .replace('%7brealurl%7d', rurl);

        //查询数据库中字段itemID为itemID对应值得记录，遍历并依次发送请求，取消非计时代办消息
        sqliteDB.selectNewTableAll(
            createTableName,
            ' itemID = ? ', [itemID],
            function(flag, data) {
                if (
                    typeof data == 'undefined' ||
                    typeof data == 'null' ||
                    data.length == 0
                ) {
                    //设置取消推送计时待办信息URL
                    const msgURL = cancelURL
                        .replace('{token}', req.query.token)
                        .replace('{timestamp}', req.query.timestamp)
                        .replace('{bizID}', bizID);

                    //打印相关参数
                    console.log('redirect URL: ' + msgURL + '\n' + 'bizID:' + bizID);

                    //发送Message请求s
                    request(msgURL, function(error, response, body) {
                        //如果请求没有报错，则返回相应结果
                        if (!error && response.statusCode == 200) {
                            //Show the HTML for the baidu homepage.
                            console.log('result info：' + body);
                            //响应:无需html响应头，默认设置text/html
                            //return res.send(body);
                        } else {
                            //return res.send(' error ');
                        }
                    });
                } else {
                    data.forEach(element => {
                        //设置取消推送计时待办信息URL
                        const msgURL = cancelURL
                            .replace('{token}', req.query.token)
                            .replace('{timestamp}', req.query.timestamp)
                            .replace('{bizID}', element.id);

                        //打印相关参数
                        console.log(
                            'redirect URL: ' + msgURL + '\n' + 'bizID:' + element.id
                        );

                        //发送Message请求
                        request(msgURL, function(error, response, body) {
                            //如果请求没有报错，则返回相应结果
                            if (!error && response.statusCode == 200) {
                                //Show the HTML for the baidu homepage.
                                console.log('result info：' + body);
                                //响应:无需html响应头，默认设置text/html
                                //return res.send(body);
                            } else {
                                //return res.send(' error ');
                            }
                        });
                    });
                }
            }
        );

        return res.send(' remove message over ');
    } catch (error) {
        console.log(error);
    }
}

/**
 * @function redirct operation function
 */
function redirctOperation(req, res) {
    try {
        //转发URL
        let redirctURL = queryRedirctURL(req, res);

        //需要记录下来的bizID，当需要取消推送消息时，需要取消
        const bizID = req.query.bizID;

        //获取itemID信息
        const itemID = req.query.itemID;

        //设置回调URL
        const rurl = redirctURL
            .replace('{itemID}', itemID)
            .replace('%7bitemID%7d', itemID)
            .replace('%7BitemID%7D', itemID);

        //SinginLogonURL
        const realURL = loginURL
            .replace('{usercode}', req.query.name)
            .replace('%7Busercode%7D', req.query.name)
            .replace('%7busercode%7d', req.query.name)
            .replace('{realurl}', rurl)
            .replace('%7Brealurl%7D', rurl)
            .replace('%7brealurl%7d', rurl);

        //跳转真实URL
        res.redirect(realURL);

        //打印真实URL
        console.log(realURL);
    } catch (error) {
        console.log(error);
    }
}

/**
 * @function query redirct url by request args
 * @param req request argments
 * @param res response info
 */
function queryRedirctURL(req, res) {
    //转发URL
    let redirctURL = redirctDFLTURL;

    try {
        if (req.query.urltype == 'QLVS') {
            //质安巡视 Quality Visits
            redirctURL = redirctQLVSURL;
        } else if (req.query.urltype == 'SPVS') {
            //预警督办 Supervising
            redirctURL = redirctSPVSURL;
        } else if (req.query.urltype == 'DSIS') {
            //示范专检  Demonstration Inspection
            redirctURL = redirctDSISURL;
        } else if (req.query.urltype == 'EGRC') {
            //工程整改 Engineering Rectification
            redirctURL = redirctEGRCURL;
        } else if (req.query.urltype == 'QLAC') {
            //品质回访 Quality Access
            redirctURL = redirctQLACURL;
        } else if (req.query.urltype == 'DLEL') {
            //交付评估 Delivery Evaluation
            redirctURL = redirctDLELURL;
        } else if (req.query.urltype == 'PDIP') {
            //生产督察 Production Inspector
            redirctURL = redirctPDIPURL;
        } else if (req.query.urltype == 'PJMN') {
            //项目维保 PJMN
            redirctURL = redirctPJMNURL;
        }
    } catch (error) {
        console.log(error);
    }

    return redirctURL;
}